﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Threading.Tasks;

namespace XHXTools
{
    /// <summary>
    /// 数据库操作类——必须指定连接字符串
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串——必须指定连接字符串
        /// </summary>
        private string connection_string { get; set; }

        /// <summary>
        /// 有参构造函数
        /// </summary>
        /// <param name="conection">数据库连接字符串</param>
        public SqlHelper(string conection)
        {
            connection_string = conection;
        }

        #region 执行格式化的SQL语句
        /// <summary>
        /// 执行增删改操作（格式化SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Update(string sql)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 返回单一结果的查询，单行单列（格式化SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>一个 int 型数据</returns>
        public object GetSingleResult(string sql)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行返回结果集查询（只读结果集）（格式化SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>一个只读结果集</returns>
        public SqlDataReader GetDataReader(string sql)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        /// <summary>
        /// 执行返回数据集的查询（格式化SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>一个数据集</returns>
        public DataSet GetDataSet(string sql)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        /// <summary>
        /// 同时执行多条查询语句，并将结果填充到对应的DataTable里面，读取时可以根据表的名称访问DataTable
        /// </summary>
        /// <param name="sqlDic">使用HashTable类型的泛型集合封装对应的SQL语句和数据表的名称</param>
        /// <returns>返回一个包含若干个数据表的数据集</returns>
        public DataSet GetDataSet(Dictionary<string, string> sqlDic)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                foreach (string tableName in sqlDic.Keys)
                {
                    cmd.CommandText = sqlDic[tableName];
                    da.Fill(ds, tableName);
                }
                return ds;
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        /// <summary>
        /// 基于ADO.NET事务提交多条增删改SQL语句
        /// </summary>
        /// <param name="sqlList">SQL语句集合</param>
        /// <returns>返回是否执行成功 int 型数据</returns>
        public int UpdateByTransaction(List<string> sqlList)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.Transaction = con.BeginTransaction();//开启事务
                int count = 0;
                //以循环的方式提交SQL语句
                foreach (string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    count += cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务（同时自动清除事务）
                return count;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();//回滚事务
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                if (cmd.Transaction != null)
                    cmd.Transaction = null;//清除事务
                con.Close();
            }
        }

        /// <summary>
        /// 执行增删改操作——[带参数][基于ADO.NET事务提交多条增删改SQL语句]
        /// </summary>
        /// <param name="sqlList">sql语句集合</param>
        /// <param name="parmList">SQLParameter集合</param>
        /// <returns></returns>
        public int UpdateByTransaction(List<string> sqlList, List<SqlParameter[]> parmList)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = con;
                con.Open();
                cmd.Transaction = con.BeginTransaction();//开启ADO事务
                int count = 0;
                for (int i = 0; i < sqlList.Count; i++)
                {
                    cmd.CommandText = sqlList[i];
                    cmd.Parameters.AddRange(parmList[i]);
                    count = count + cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务
                return count;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();//回滚事务
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\t SQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                if (cmd.Transaction != null)
                    cmd.Transaction = null;//清除事务
                con.Close();
            }
        }

        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行增删改操作（带参数SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Update(string sql, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 返回单一结果的查询，单行单列（带参数SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object GetSingleResult(string sql, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行返回结果集查询（只读结果集）（带参数SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string sql, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        /// <summary>
        /// 执行返回数据集的查询（带参数SQL语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, SqlParameter[] param)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddRange(param);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        #endregion

        #region 调用存储过程

        /// <summary>
        ///  执行增删改操作（存储过程）
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int UpdateByProcedure(string proc, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;//当前的操作是执行存储过程
            cmd.CommandText = proc;
            try
            {
                con.Open();
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行返回结果集查询（存储过程）
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReaderByProcedure(string proc, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand();
            //cmd.CommandTimeout = 10000 * 100;
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;//当前的操作是执行存储过程
            cmd.CommandText = proc;
            try
            {
                con.Open();
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        /// <summary>
        /// 执行数据集查询（存储过程）
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSetByProcedure(string proc, SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connection_string);
            SqlCommand cmd = new SqlCommand(proc, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                string time = GetDBServerTime().ToString();
                string logTxt = $"时间：【{time}】\n\tSQL执行时出现错误，具体信息：{ex.Message}\n";
                //将错误信息写入日志文件
                RWTextToFile.WriterLog(logTxt, Path.Combine(AppContext.BaseDirectory, "Logs"), "SqlError.log");
                throw ex;
            }
        }

        #endregion

        #region 获取数据库服务器的时间、将错误信息写入日志

        /// <summary>
        /// 获取数据库服务器时间
        /// </summary>
        /// <returns>返回时间</returns>
        public DateTime GetDBServerTime()
        {
            string sql = "select getdate()";
            return Convert.ToDateTime(GetSingleResult(sql));
        }


        /// <summary>
        /// 获得服务器IP地址（不咋的，待写新的）
        /// </summary>
        /// <returns>一个字典</returns>
        public Dictionary<string, string> GetHostName()
        {
            try
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                string name = Dns.GetHostName();
                dic.Add("hostName", name);
                string addressIP = string.Empty;
                IPAddress[] _addressIP = Dns.GetHostAddresses(name);
                foreach (var item in _addressIP)
                {
                    if (item.AddressFamily.ToString() == "InterNetwork")
                    {
                        addressIP = item.ToString();
                        dic.Add("addressIP", addressIP);
                    }
                }
                return dic;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
